﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using Models;
using IDAL;


namespace DAL
{
    public class NewsInfoDAL:INewsInfo
    {
        //private const string SQL_ALL_NewInfo = @"select * from NewsInfo";
        private const string SQL_NewInfo = @"select * from NewsInfo where NewsID = ";
        private const string SQL_One_NewsInfo = @"select * from NewsInfo where NewsID = ";
        private const string SQL_DECLARE = @"declare @precount int ";
        private const string SQL_PRE_COUNT = @"select @precount = count(*) from NewsInfo where NewsID < ";
        private const string SQL_NEXT_COUNT = @"select @precount = count(*) from NewsInfo where NewsID > ";
        private const string SQL_PRE_NEWSINFO = @"select top 1 NewsID, CategoryID,@precount as precount from NewsInfo where NewsID <";
        private const string SQL_NEXT_NEWINFO = @"select top 1 NewsID, CategoryID,@precount as precount from NewsInfo where NewsID > ";
        private const string SQL_ORDER_BY = @" order by NewsID desc";
        private const string SQL_CATERGORYID = " and CategoryID =";
        private const string SQL_NewsInfo_CountAdd = @"UPDATE [NewsInfo] SET [ClickCount] = [ClickCount] + 1 WHERE NewsID = ";

        public DataBase DB = new DataBase();

        public ArrayList GetPreNewsInfo(int nowNewsID,int categoryID)
        {
            ArrayList list = new ArrayList();
            DB.DatabaseConnection();
            string presql = "";
            if (categoryID == 0)
            {
                presql = SQL_DECLARE + SQL_NEXT_COUNT + nowNewsID + SQL_NEXT_NEWINFO + nowNewsID;
            }
            else
            {
                presql = SQL_DECLARE + SQL_NEXT_COUNT + nowNewsID + SQL_NEXT_NEWINFO + nowNewsID + SQL_CATERGORYID + categoryID;
            }
            SqlDataReader reader = DB.GetdataRecords(presql);
            try
            {
                if (reader.Read())
                {
                    list.Add(Int32.Parse(reader["NewsID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["CategoryID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["precount"].ToString().Trim()));
                }
                else
                {
                    list.Add(0);
                    list.Add(0);
                    list.Add(0);
                }
            }
            catch
            {
                list.Add(0);
                list.Add(0);
                list.Add(0);
            }
            DB.CloseDB();
            return list;
        }

        public ArrayList GetNextNewsInfo(int nowNewsID,int categoryID)
        {
            ArrayList list = new ArrayList();
            DB.DatabaseConnection();
            string nextSQL = "";
            if (categoryID == 0)
            {
                nextSQL = SQL_DECLARE + SQL_PRE_COUNT + nowNewsID + SQL_PRE_NEWSINFO + nowNewsID + SQL_ORDER_BY;
            }
            else
            {
                nextSQL = SQL_DECLARE + SQL_PRE_COUNT + nowNewsID + SQL_PRE_NEWSINFO + nowNewsID + SQL_CATERGORYID + categoryID + SQL_ORDER_BY;
            }
            SqlDataReader reader = DB.GetdataRecords(nextSQL);
            try
            {
                if (reader.Read())
                {
                    list.Add(Int32.Parse(reader["NewsID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["CategoryID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["precount"].ToString().Trim()));
                }
                else
                {
                    list.Add(0);
                    list.Add(0);
                    list.Add(0);
                }
            }
            catch
            {
                list.Add(0);
                list.Add(0);
                list.Add(0);
            }
            DB.CloseDB();
            return list;
        }

        public int GetPageCount(int pagesize,int CategoryID)
        {
            DB.DatabaseConnection();
            DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            DB.SQLCommand.CommandType = CommandType.StoredProcedure;
            if (CategoryID == 0)
            {
                DB.SQLCommand.CommandText = "UP_NewsInfo_PAGECOUNT";
                DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
            }
            else
            {
                DB.SQLCommand.CommandText = "UP_NewsCategoryPageCount_PAGECOUNT";
                DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
                DB.SQLCommand.Parameters.AddWithValue("@categoryid", CategoryID);
            }
         
            DB.SQLCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            DB.SQLCommand.ExecuteNonQuery();
            int n = (int)DB.SQLCommand.Parameters["@RETURN_VALUE"].Value;
            DB.CloseDB();
            return n;
        }

        //用于index页面的news的数据查询
        public List<NewsInfo> GetPageNewsInfo()
        {
            List<NewsInfo> list = new List<NewsInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            //DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_NewsInfo_PAGESELECT";
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                NewsInfo newsInfo = new NewsInfoList();
                ReadtoNewsInfo(reader, newsInfo);
                list.Add(newsInfo);
            }
            DB.CloseDB();
            return list;
        }

        //用于news页面的全部news数据查询
        public List<NewsInfo> GetAllNewsInfo(int pagesize, int nowpage)
        {
            List<NewsInfo> list = new List<NewsInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            //DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_NewsInfo_ALLSELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                NewsInfo newsInfo = new NewsInfoList();
                ReadtoNewsInfo(reader, newsInfo);
                list.Add(newsInfo);
            }
            DB.CloseDB();
            return list;
        }

        //用于news页面news的分类查询
        public List<NewsInfo> GetCategoryNewsInfo(int CategoryID, int pagesize, int nowpage)
        {
            List<NewsInfo> list = new List<NewsInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_CategoryNewsInfo_PAGESELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@categoryid", CategoryID);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                NewsInfo newsInfo = new NewsInfo();
                ReadtoNewsInfo(reader, newsInfo);
                list.Add(newsInfo);
            }
            DB.CloseDB();
            return list;
        }

        public NewsInfo GetOneNewsInfo(int NewsID)
        {
            NewsInfo newsInfo = new NewsInfoList();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_One_NewsInfo + NewsID);
            if (reader.HasRows && reader.Read())
            {
                ReadtoNewsInfo(reader, newsInfo);
            }
            DB.CloseDB();
            return newsInfo;
        }

        public List<NewsInfo> GetNewsInfo(int NewsID)
        {
            List<NewsInfo> list = new List<NewsInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_NewInfo + NewsID);
            if(reader.Read())
            {
                NewsInfo newsInfo = new NewsInfo();
                ReadtoNewsInfo(reader, newsInfo);
                list.Add(newsInfo);
            }
            DB.CloseDB();
            return list;
        }

        public void ReadtoNewsInfo(SqlDataReader reader, NewsInfo newsInfo)
        {
            try
            {
                newsInfo.NewsID = Int32.Parse(reader["NewsID"].ToString());
            }
            catch
            { }
            try
            {
                newsInfo.CategoryID = Int32.Parse(reader["CategoryID"].ToString());
            }
            catch
            { }
            newsInfo.Title = reader["Title"].ToString();
            newsInfo.NewsContent = reader["NewsContent"].ToString();
            try
            {
                newsInfo.PublishTime = (DateTime)reader["PublishTime"];
            }
            catch
            { }
            try
            {
                newsInfo.TopFlag = Int32.Parse(reader["TopFlag"].ToString());
            }
            catch
            { }
            try
            {
                newsInfo.HotFlag = Int32.Parse(reader["HotFlag"].ToString());
            }
            catch
            { }
            try
            {
                newsInfo.ClickCount = Int32.Parse(reader["ClickCount"].ToString());
            }
            catch
            { }
            try
            {
                newsInfo.NewFlag = Int32.Parse(reader["NewFlag"].ToString());
            }
            catch
            { }
            newsInfo.NewsPicURL =reader["NewsPicURL"] is DBNull?"":reader["NewsPicURL"].ToString();
            newsInfo.NewsBrief = reader["NewsBrief"] is DBNull ? "" : reader["NewsBrief"].ToString();
        }

        public int AddNewsInfo(NewsInfo news)
        {
            int isSucess = -1;
            string sql = @" INSERT INTO NewsInfo([CategoryID],[Title],[NewsContent],[PublishTime],[TopFlag],[HotFlag],[NewsPicURL],[ClickCount],[NewFlag],[NewsBrief]) 
                                    VALUES(" + news.CategoryID + ",'" + news.Title + "','" + news.NewsContent + "','" + news.PublishTime + "'," + news.TopFlag + "," + news.HotFlag + ",'" + news.NewsPicURL + "',0," + news.NewFlag + ",'" + news.NewsBrief + "')";
            isSucess = DB.UpdateRecords(sql);
            return isSucess;
        }

        public int UpdateNewsInfo(NewsInfo news)
        {
            int isSuccess = -1;
            string sql = @"UPDATE NewsInfo
                                SET 
                                    [CategoryID] = " + news.CategoryID + @",
                                    [Title]            = '" + news.Title + @"',
                                    [NewsContent] = '" + news.NewsContent + @"',
                                    [PublishTime] = '" + news.PublishTime + @"',
                                    [TopFlag]      = " + news.TopFlag + @",
                                    [HotFlag]      = " + news.HotFlag + @",
                                    [NewsPicURL] = '" + news.NewsPicURL + @"',
                                    [NewsBrief] =  '" + news.NewsBrief + @"'
                                WHERE NewsID = " + news.NewsID;

            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int NewsInfoCountAdd(int NewsID)
        {
            int isSuccess = -1;
            string sql = SQL_NewsInfo_CountAdd+NewsID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int DeleteNewsInfo(int NewsID)
        {
            int isSuccess = -1;
            string sql = @"DELETE FROM  NewsInfo WHERE NewsID = " + NewsID + " ";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        /// <summary>
        /// 获取指定新闻分类中的新闻数目
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public int GetCategoryNewsInfoCount(int CategoryID)
        {
            int count = 0;
            String sql = "SELECT COUNT(NewsID) AS COUNT FROM NewsInfo WHERE CategoryID = " + CategoryID;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            if (reader.Read())
            {
                try{
                    count = (int)reader["COUNT"];
                }catch{

                }
            }
            DB.CloseDB();
            return count;
        }
    }
}
